package test1;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.swing.JOptionPane;
/*
 * http://www.tutorialspoint.com/jdbc/jdbc-select-database.htm
 * http://www.tutorialspoint.com/sqlite/sqlite_java.htm
 * http://javastart.pl/zaawansowane-programowanie/bazy-danych-sqlite-w-javie/
 */

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;


public class SQLiteJDBC {
	
	private static Logger log = LogManager.getLogger(SQLiteJDBC.class);

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		log.debug("Start");
		
		Connection conn = null;
		Statement stmt = null;
		
	    try {
	      Class.forName("org.sqlite.JDBC");
	      log.debug("Connecting");
//	      conn = DriverManager.getConnection("jdbc:sqlite://10.10.1.117/sqltest/test.db");
	      conn = DriverManager.getConnection("jdbc:sqlite:test.db");
//	      conn = DriverManager.getConnection("jdbc:sqlite:d:/Robert/IFJ/Projects_svn/SQLITECPP/SQLITECPP/test.db");
	      
	      log.debug("Connected");
	      stmt = conn.createStatement();
	
	      //====
	      String sql = "CREATE TABLE IF NOT EXISTS REGISTRATION " +
                  "(id INTEGER not NULL, " +
                  " first VARCHAR(255), " + 
                  " last VARCHAR(255), " + 
                  " age INTEGER, " + 
                  " PRIMARY KEY ( id ))";
	      stmt.executeUpdate(sql);
	      //=====
	      
	      //==============
	      log.debug("Insert Start");
	       sql = "INSERT INTO Registration " +
	      " (first,last,age) " +
                  "VALUES ('ZaraZaraZaraZaraZara', 'Ali', 18)";	
	      
	//      for (int i =0; i<7000; i++){
	      stmt.executeUpdate("PRAGMA synchronous=OFF");
	//      for (int i =0; i<100; i++)
	      stmt.executeUpdate(sql);
	//      System.exit(0);
	//      stmt.executeUpdate(sql);
	      stmt.executeUpdate(sql);
	//      stmt.executeUpdate(sql);
	//      stmt.executeUpdate("commit");
	//      }

	      log.debug("Insert Stop");
	      //==============
	      log.debug("Select Start");
	      sql = "SELECT * FROM Registration ";
	      ResultSet rs = stmt.executeQuery(sql);
//	      while(rs.next()){
//	          //Retrieve by column name
//	          int id  = rs.getInt("id");
//	          int age = rs.getInt("age");
//	          String first = rs.getString("first");
//	          String last = rs.getString("last");
//
//	          //Display values
//	          System.out.print("ID: " + id);
//	          System.out.print(", Age: " + age);
//	          System.out.print(", First: " + first);
//	          System.out.println(", Last: " + last);
//	       }
	      
	      log.debug("Select End");
	      
	      sql = "SELECT last_insert_rowid()  FROM Registration ";

	     
	      rs= stmt.executeQuery(sql);
	      System.out.println(rs.getInt(1));
	      
	    } catch ( Exception e ) {
	      System.err.println( e.getClass().getName() + ": " + e.getMessage() );
	      e.printStackTrace();
	      System.exit(0);
	    }
	    System.out.println("Opened database successfully");
	    log.debug("Stop");
	}

}
